﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using WcfService.ModelLayer;

namespace WcfService.DBLayer
{
    public class DBVotingResults: IDBVotingResults
    {
        private string tableVotingResults = "dbo.VotingResults";
        private string tableAppUsers = "dbo.AppUsers";

        public int SaveResult(VotingResult votingResult)
        {
            string sql = "INSERT INTO " + tableVotingResults + " (episodeId, option1, option2) VALUES (@EpisodeId, @Option1, @Option2)";
        
            SqlCommand cmd = DBConnection.GetDbCommand(sql);

            cmd.Parameters.AddWithValue("@EpisodeId", votingResult.EpisodeId);
            cmd.Parameters.AddWithValue("@Option1", votingResult.OptionOne);
            cmd.Parameters.AddWithValue("@Option2", votingResult.OptionTwo);

            int result = cmd.ExecuteNonQuery();

            DBConnection.Close();

            return result;
        }

        public int SaveRegId(string regId)
        {
            string sql = "INSERT INTO " + tableAppUsers + " (RegId) VALUES (@RegId)";

            SqlCommand cmd = DBConnection.GetDbCommand(sql);

            cmd.Parameters.AddWithValue("@RegId", regId);

            int result = cmd.ExecuteNonQuery();

            DBConnection.Close();

            return result;
        }

        public int SaveResultV()
        {
            string sql = "INSERT INTO " + tableVotingResults + " (episodeId, option1, option2) VALUES (@EpisodeId, @Option1, @Option2)";

            SqlCommand cmd = DBConnection.GetDbCommand(sql);

            cmd.Parameters.AddWithValue("@EpisodeId", 1);
            cmd.Parameters.AddWithValue("@Option1", 100);
            cmd.Parameters.AddWithValue("@Option2", 900);

            int result = cmd.ExecuteNonQuery();

            DBConnection.Close();

            return result;
        }

        public int GetResultOpt1(int episodeId)
        {
         
            int result = 0;

            string sql = "SELECT option1 FROM VotingResults WHERE episodeId = " + episodeId;

            SqlCommand cmd = DBConnection.GetDbCommand(sql);

            SqlDataReader reader = cmd.ExecuteReader();
            {

                if (reader.Read())
                {
                    result = (int)reader["option1"];
                    reader.Close();
                }
            }

            DBConnection.Close();

            return result;
        }

        public int GetResultOpt2(int episodeId)
        {
            int result = 0;

            string sql = "SELECT option2 FROM VotingResults WHERE episodeId = " + episodeId;

            SqlCommand cmd = DBConnection.GetDbCommand(sql);

            SqlDataReader reader = cmd.ExecuteReader();
            {

                if (reader.Read())
                {
                    result = (int)reader["option2"];
                    reader.Close();
                }
            }

            DBConnection.Close();

            return result;
        }

        public string GetOptionText1(int episodeId)
        {

            string optionText = "Ingen svarmulighed";

            string sql = "SELECT option1 FROM Options WHERE episodeId = " + episodeId;

            SqlCommand cmd = DBConnection.GetDbCommand(sql);

            SqlDataReader reader = cmd.ExecuteReader();
            {

                if (reader.Read())
                {
                    optionText = (string)reader["option1"];
                    reader.Close();
                }
            }

            DBConnection.Close();

            return optionText;
        }

        public string GetOptionText2(int episodeId)
        {

            string optionText = "Ingen svarmulighed";

            string sql = "SELECT option2 FROM Options WHERE episodeId = " + episodeId;

            SqlCommand cmd = DBConnection.GetDbCommand(sql);

            SqlDataReader reader = cmd.ExecuteReader();
            {

                if (reader.Read())
                {
                    optionText = (string)reader["option2"];
                    reader.Close();
                }
            }

            DBConnection.Close();

            return optionText;
        }
    }
}
